﻿Public Class TMySystemView
    Inherits TCustomMyObject
    Implements IHasColumns

    Private _Limit As Integer = 0
    Private _Offset As Integer = 0
    Friend _Owner As TCustomMyObjects
    Friend _Triggers As New List(Of TMyTrigger)
    Friend _Columns As TMyTableColumns
    Friend _TableCatalog As String
    Friend _TableSchema As String
    Friend _TableName As String
    Friend _TableType As String
    Friend _Engine As String
    Friend _Version As Integer
    Friend _RowFormat As String
    Friend _TableRows As Integer
    Friend _AvgRowLength As Integer
    Friend _DataLength As Integer
    Friend _MaxDataLength As Integer
    Friend _IndexLength As Integer
    Friend _DataFree As Integer
    Friend _AutoIncrement As Integer
    Friend _CreateTime As DateTime
    Friend _UpdateTime As DateTime
    Friend _CheckTime As DateTime
    Friend _TableCollation As String
    Friend _Checksum As Integer
    Friend _CreateOptions As String
    Friend _TableComment As String
    Friend _Conditions As String = String.Empty

    Public Function GetRowCount() As Integer Implements IHasColumns.GetRowCount
        MyQuery.Close()
        MyQuery.Clear()
        MyQuery.SQL.AppendLine("select")
        MyQuery.SQL.AppendLine(" count(*) as cnt")
        MyQuery.SQL.AppendLine("from")
        MyQuery.SQL.Append(" `").Append(Me.DatabaseName).Append("`.`").AppendLine(Me.ObjectName)
        MyQuery.Execute()
        Try
            Return MyQuery.FieldAsInteger("cnt")
        Finally
            MyQuery.Close()
        End Try
    End Function

    Friend Sub New(ByVal AOwner As TCustomMyObjects, ByVal AObjectName As String)
        MyBase.New(AOwner, AObjectName)
        _Owner = AOwner
        _Columns = New TMyTableColumns(Me)
    End Sub

    Public Overrides ReadOnly Property Source() As String Implements IHasColumns.Source
        Get
            MyQuery.Clear()
            MyQuery.SQL.AppendLine("show create table `" & Me.DatabaseName & "`.`" & Me.ObjectName & "`")
            MyQuery.Execute()
            Try
                If Not MyQuery.Eof Then
                    Return MyQuery.FieldAsString("create table")
                Else
                    Throw New Exception
                End If
            Finally
                MyQuery.Close()
            End Try
        End Get
    End Property

    Public ReadOnly Property TableCatalog() As String
        Get
            Return _TableCatalog
        End Get
    End Property

    Public ReadOnly Property TableSchema() As String Implements IHasColumns.TableSchema
        Get
            Return _TableSchema
        End Get
    End Property

    Public ReadOnly Property TableName() As String Implements IHasColumns.TableName
        Get
            Return _TableName
        End Get
    End Property

    Public ReadOnly Property TableType() As String
        Get
            Return _TableType
        End Get
    End Property

    Public ReadOnly Property Engine() As String
        Get
            Return _Engine
        End Get
    End Property

    Public ReadOnly Property Version() As Integer
        Get
            Return _Version
        End Get
    End Property

    Public ReadOnly Property RowFormat() As String
        Get
            Return _RowFormat
        End Get
    End Property

    Public ReadOnly Property TableRows() As Integer
        Get
            Return _TableRows
        End Get
    End Property

    Public ReadOnly Property AvgRowLength() As Integer
        Get
            Return _AvgRowLength
        End Get
    End Property

    Public ReadOnly Property DataLength() As Integer
        Get
            Return _DataLength
        End Get
    End Property

    Public ReadOnly Property MaxDataLength() As Integer
        Get
            Return _MaxDataLength
        End Get
    End Property

    Public ReadOnly Property IndexLength() As Integer
        Get
            Return _IndexLength
        End Get
    End Property

    Public ReadOnly Property DataFree() As Integer
        Get
            Return _DataFree
        End Get
    End Property

    Public ReadOnly Property AutoIncrement() As Integer
        Get
            Return _AutoIncrement
        End Get
    End Property

    Public ReadOnly Property CreateTime() As DateTime
        Get
            Return _CreateTime
        End Get
    End Property

    Public ReadOnly Property UpdateTime() As DateTime
        Get
            Return _UpdateTime
        End Get
    End Property

    Public ReadOnly Property CheckTime() As DateTime
        Get
            Return _CheckTime
        End Get
    End Property

    Public ReadOnly Property TableCollation() As String
        Get
            Return _TableCollation
        End Get
    End Property

    Public ReadOnly Property Checksum() As Integer
        Get
            Return _Checksum
        End Get
    End Property

    Public ReadOnly Property CreateOptions() As String
        Get
            Return _CreateOptions
        End Get
    End Property

    Public ReadOnly Property TableComment() As String Implements IHasColumns.TableComment
        Get
            Return _TableComment
        End Get
    End Property
    Public ReadOnly Property Columns() As TMyTableColumns Implements IHasColumns.Columns
        Get
            Return _Columns
        End Get
    End Property

    Public ReadOnly Property Columns(ByVal Index As Integer) As TMyTableColumn Implements IHasColumns.Columns
        Get
            Return _Columns(Index)
        End Get
    End Property

    Public ReadOnly Property Query() As TMyQuery Implements IHasColumns.Query
        Get
            Return Me.MyQuery
        End Get
    End Property

    Public ReadOnly Property Triggers(ByVal Index As Integer) As TMyTrigger Implements IHasColumns.Triggers
        Get
            Return _Triggers(Index)
        End Get
    End Property

    Public ReadOnly Property TriggerCount() As Integer Implements IHasColumns.TriggerCount
        Get
            Return _Triggers.Count
        End Get
    End Property

    Public Sub RefreshTrigger() Implements IHasColumns.RefreshTrigger
        Dim item As TMyTrigger
        _Triggers.Clear()
        MyQuery.Clear()
        MyQuery.SQL.AppendLine("SELECT")
        MyQuery.SQL.AppendLine(" TRIGGER_CATALOG")
        MyQuery.SQL.AppendLine(",TRIGGER_SCHEMA")
        MyQuery.SQL.AppendLine(",TRIGGER_NAME")
        MyQuery.SQL.AppendLine(",EVENT_MANIPULATION")
        MyQuery.SQL.AppendLine(",EVENT_OBJECT_CATALOG")
        MyQuery.SQL.AppendLine(",EVENT_OBJECT_SCHEMA")
        MyQuery.SQL.AppendLine(",EVENT_OBJECT_TABLE")
        MyQuery.SQL.AppendLine(",ACTION_ORDER")
        MyQuery.SQL.AppendLine(",ACTION_CONDITION")
        MyQuery.SQL.AppendLine(",ACTION_STATEMENT")
        MyQuery.SQL.AppendLine(",ACTION_ORIENTATION")
        MyQuery.SQL.AppendLine(",ACTION_TIMING")
        MyQuery.SQL.AppendLine(",ACTION_REFERENCE_OLD_TABLE")
        MyQuery.SQL.AppendLine(",ACTION_REFERENCE_NEW_TABLE")
        MyQuery.SQL.AppendLine(",ACTION_REFERENCE_OLD_ROW")
        MyQuery.SQL.AppendLine(",ACTION_REFERENCE_NEW_ROW")
        MyQuery.SQL.AppendLine(",CREATED")
        MyQuery.SQL.AppendLine(",SQL_MODE")
        MyQuery.SQL.AppendLine(",DEFINER")
        MyQuery.SQL.AppendLine(",CHARACTER_SET_CLIENT")
        MyQuery.SQL.AppendLine(",COLLATION_CONNECTION")
        MyQuery.SQL.AppendLine(",DATABASE_COLLATION")
        MyQuery.SQL.AppendLine("FROM")
        MyQuery.SQL.AppendLine(" information_schema.triggers")
        MyQuery.SQL.AppendLine("where")
        MyQuery.SQL.AppendLine("EVENT_OBJECT_SCHEMA=?table_schema")
        MyQuery.SQL.AppendLine("AND EVENT_OBJECT_TABLE=?table_name")
        MyQuery.SQL.AppendLine("ORDER BY")
        MyQuery.SQL.AppendLine(" ACTION_TIMING")
        MyQuery.SQL.AppendLine(",EVENT_MANIPULATION")
        MyQuery.DeclareAndSet("table_schema", Me.TableSchema)
        MyQuery.DeclareAndSet("table_name", Me.TableName)
        MyQuery.Execute()
        While Not MyQuery.Eof
            item = New TMyTrigger(_Owner, MyQuery.FieldAsString("TRIGGER_NAME"))
            item._TriggerCatalog = MyQuery.FieldAsString("trigger_catalog")
            item._TriggerSchema = MyQuery.FieldAsString("trigger_schema")
            item._TriggerName = MyQuery.FieldAsString("trigger_name")
            item._EventManipulation = MyQuery.FieldAsString("event_manipulation")
            item._EventObjectCatalog = MyQuery.FieldAsString("event_object_catalog")
            item._EventObjectSchema = MyQuery.FieldAsString("event_object_schema")
            item._EventObjectTable = MyQuery.FieldAsString("event_object_table")
            item._ActionOrder = MyQuery.FieldAsInteger("action_order")
            item._ActionCondition = MyQuery.FieldAsString("action_condition")
            item._ActionStatement = MyQuery.FieldAsString("action_statement")
            item._ActionOrientation = MyQuery.FieldAsString("action_orientation")
            item._ActionTiming = MyQuery.FieldAsString("action_timing")
            item._ActionReferenceOldTable = MyQuery.FieldAsString("action_reference_old_table")
            item._ActionReferenceNewTable = MyQuery.FieldAsString("action_reference_new_table")
            item._ActionReferenceOldRow = MyQuery.FieldAsString("action_reference_old_row")
            item._ActionReferenceNewRow = MyQuery.FieldAsString("action_reference_new_row")
            item._Created = MyQuery.FieldAsDateTime("created")
            item._SqlMode = MyQuery.FieldAsString("sql_mode")
            item._Definer = MyQuery.FieldAsString("definer")
            item._CharacterSetClient = MyQuery.FieldAsString("character_set_client")
            item._CollationConnection = MyQuery.FieldAsString("collation_connection")
            item._DatabaseCollation = MyQuery.FieldAsString("database_collation")
            _Triggers.Add(item)
            MyQuery.MoveNext()
        End While
        MyQuery.Close()

    End Sub

    Public ReadOnly Property ColumnCount() As Integer Implements IHasColumns.ColumnCount
        Get
            Return _Columns.Count
        End Get
    End Property

    Public Sub RefreshColumn() Implements IHasColumns.RefreshColumn
        _Columns.Refresh()
    End Sub

    Public ReadOnly Property DataTable() As System.Data.DataTable Implements IHasColumns.DataTable
        Get
            Return MyQuery.DataSet.Tables(0)
        End Get
    End Property

    Public Sub RefreshData() Implements IHasColumns.RefreshData
        MyQuery.Close()
        MyQuery.Clear()
        MyQuery.SQL.AppendLine("select")
        MyQuery.SQL.AppendLine(" *")
        MyQuery.SQL.AppendLine("from")
        MyQuery.SQL.AppendLine(" `" & Me.TableSchema & "`.`" & Me.TableName & "`")
        If _Conditions.Length > 0 Then
            MyQuery.SQL.AppendLine("where")
            MyQuery.SQL.AppendLine("1=1")
            MyQuery.SQL.Append(_Conditions)
        End If
        If _Limit > 0 Then
            MyQuery.SQL.AppendLine("limit " & _Limit.ToString)
            MyQuery.SQL.AppendLine("offset " & _Offset.ToString)
        End If
        MyQuery.Execute()
    End Sub

    Public Property Limit() As Integer Implements IHasColumns.Limit
        Get
            Return _Limit
        End Get
        Set(ByVal value As Integer)
            _Limit = value
        End Set
    End Property

    Public Property Offset() As Integer Implements IHasColumns.Offset
        Get
            Return _Offset
        End Get
        Set(ByVal value As Integer)
            _Offset = value
        End Set
    End Property

    Public Sub CopyData() Implements IHasColumns.CopyData
        System.Windows.Forms.Clipboard.SetText(MyQuery.DataSetText(TMyQuery.TColSeparator.csTab))
    End Sub

    Public Sub RefreshAll() Implements IHasColumns.RefreshAll
        RefreshColumn()
        RefreshTrigger()
        RefreshDefinition()
    End Sub

    Public Sub SaveData(ByVal AFileName As String) Implements IHasColumns.SaveData
        Dim sw As New System.IO.StreamWriter(AFileName, False, System.Text.Encoding.UTF8)
        Try
            sw.Write(MyQuery.DataSetText(TMyQuery.TColSeparator.csTab))
            sw.Flush()
        Finally
            sw.Close()
            sw.Dispose()
        End Try
    End Sub

    Public Sub RefreshDefinition() Implements IHasColumns.RefreshDefinition
        MyQuery.Clear()
        MyQuery.SQL.AppendLine("select")
        MyQuery.SQL.AppendLine(" table_catalog")
        MyQuery.SQL.AppendLine(",table_schema")
        MyQuery.SQL.AppendLine(",table_name")
        MyQuery.SQL.AppendLine(",table_type")
        MyQuery.SQL.AppendLine(",engine")
        MyQuery.SQL.AppendLine(",version")
        MyQuery.SQL.AppendLine(",row_format")
        MyQuery.SQL.AppendLine(",table_rows")
        MyQuery.SQL.AppendLine(",avg_row_length")
        MyQuery.SQL.AppendLine(",data_length")
        MyQuery.SQL.AppendLine(",max_data_length")
        MyQuery.SQL.AppendLine(",index_length")
        MyQuery.SQL.AppendLine(",data_free")
        MyQuery.SQL.AppendLine(",auto_increment")
        MyQuery.SQL.AppendLine(",create_time")
        MyQuery.SQL.AppendLine(",update_time")
        MyQuery.SQL.AppendLine(",check_time")
        MyQuery.SQL.AppendLine(",table_collation")
        MyQuery.SQL.AppendLine(",checksum")
        MyQuery.SQL.AppendLine(",create_options")
        MyQuery.SQL.AppendLine(",table_comment")
        MyQuery.SQL.AppendLine("from")
        MyQuery.SQL.AppendLine(" information_schema.tables")
        MyQuery.SQL.AppendLine("where")
        MyQuery.SQL.AppendLine("table_schema=?table_schema")
        MyQuery.SQL.AppendLine("and table_name=?table_name")
        MyQuery.SQL.AppendLine("and table_type like '%SYSTEM VIEW%'")

        MyQuery.DeclareAndSet("table_schema", Me.DatabaseName)
        MyQuery.DeclareAndSet("table_name", Me.TableName)
        MyQuery.Execute()
        If Not MyQuery.Eof Then
            _TableCatalog = MyQuery.FieldAsString("table_catalog")
            _TableSchema = MyQuery.FieldAsString("table_schema")
            _TableName = MyQuery.FieldAsString("table_name")
            _TableType = MyQuery.FieldAsString("table_type")
            _Engine = MyQuery.FieldAsString("engine")
            _Version = MyQuery.FieldAsInteger("version")
            _RowFormat = MyQuery.FieldAsString("row_format")
            _TableRows = MyQuery.FieldAsInteger("table_rows")
            _AvgRowLength = MyQuery.FieldAsInteger("avg_row_length")
            _DataLength = MyQuery.FieldAsInteger("data_length")
            _MaxDataLength = MyQuery.FieldAsInteger("max_data_length")
            _IndexLength = MyQuery.FieldAsInteger("index_length")
            _DataFree = MyQuery.FieldAsInteger("data_free")
            _AutoIncrement = MyQuery.FieldAsInteger("auto_increment")
            _CreateTime = MyQuery.FieldAsDateTime("create_time")
            _UpdateTime = MyQuery.FieldAsDateTime("update_time")
            _CheckTime = MyQuery.FieldAsDateTime("check_time")
            _TableCollation = MyQuery.FieldAsString("table_collation")
            _Checksum = MyQuery.FieldAsInteger("checksum")
            _CreateOptions = MyQuery.FieldAsString("create_options")
            _TableComment = MyQuery.FieldAsString("table_comment")
        End If
        MyQuery.Close()

    End Sub

    Public Property Conditions() As String Implements IHasColumns.Conditions
        Get
            Return _Conditions
        End Get
        Set(ByVal value As String)
            _Conditions = value
        End Set
    End Property
    Private _PrefixLength As Integer
    Public Property PrefixLength() As Integer Implements IHasColumns.PrefixLength
        Get
            Return _PrefixLength
        End Get
        Set(ByVal value As Integer)
            _PrefixLength = value
        End Set
    End Property

    Public Function GetCardinarySet(ByVal AColumnName As String) As System.Data.DataTable Implements IHasColumns.GetCardinarySet
        MyQuery.Clear()
        MyQuery.SQL.AppendLine("select")
        MyQuery.SQL.Append(" count(distinct ").Append(AColumnName).Append(") / count(*) as c0")
        For i = 1 To _PrefixLength
            MyQuery.SQL.Append(",count(distinct left(").Append(AColumnName).Append(",").Append(i.ToString).Append(")) / count(*) as c").AppendLine(i.ToString)
        Next
        MyQuery.SQL.AppendLine("from")
        MyQuery.SQL.Append(" `").Append(_TableSchema).Append("`.`").Append(_TableName).AppendLine("`")
        MyQuery.Execute()
        Try
            Dim dtResult As DataTable = MyQuery.DataSet.Tables(0)
            Dim dt As DataTable = New DataTable
            dt.Columns.Add("count", GetType(String))
            dt.Columns.Add("value", GetType(Decimal))
            For i As Integer = 0 To dtResult.Columns.Count - 1
                Dim row As DataRow = dt.NewRow
                row("count") = dtResult.Columns("c" & i.ToString).ColumnName
                row("value") = dtResult.Rows(0).Item(i)
                dt.Rows.Add(row)
            Next
            Return dt
        Finally
            MyQuery.Close()
        End Try

    End Function

End Class
